package support;

import java.io.*;
import java.util.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class support{
    public Vector getCountries(){
        countries c = new countries();
        return c.getVector("SELECT country FROM countries");
    }

    public Vector getStateofUniversities(){
        universities u = new universities();
        return u.getStateVector();
    }

    public Vector getUniversities(String state){
    	universities u = new universities();
    	return u.initUniv(state);
    }

    //the logic for majors and specializations are exactly
    //the same as for countries so just reuse the class
    public Vector getMajors(){
	        countries m = new countries();
	        return m.getVector("SELECT discipline From disciplines");
    }

    public Vector getSpecializations(){
	        countries s = new countries();
	        return s.getVector("SELECT specialization From specializations");
    }
    
    public void initDate(String path){
    	universities u = new universities();
    	
    	u.initDatabase(path);
    }
}

class countries{
    Vector c = new Vector();

    public Vector getVector(String sql){
        if(c.isEmpty())
            this.initVector(sql);
        return c;
    }

    void initVector(String sql){
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rset=
			stmt.executeQuery(sql);
			// Print out the PID (1st attribute)
			while (rset.next())
				 c.add(rset.getString(1));
			//close the result set, statement, and connection
			rset.close();
			stmt.close();
		}
		catch (ServiceException e) {
			throw e;
		} catch (Exception e) {
			DBUtils.rollback(conn);
			throw new ServiceException("error occur inquiring countries");
		} finally {
			DBUtils.closeConnection(conn);
		}
    }
}

class universities{
    Vector v = new Vector();
    Vector s = new Vector();
    Vector u = new Vector();

    public Vector getStateVector(){
        if(s.isEmpty())
            this.initStates();
        return s;
    }

    void initStates(){
    	Connection conn = null;
    	try {
    		conn = DBUtils.getConnection();
	    	Statement stmt = conn.createStatement();
			ResultSet rset=
			stmt.executeQuery("SELECT DISTINCT location FROM universities ORDER BY location");
			while (rset.next()){
				s.add(rset.getString(1));
			}
			//close the result set, statement, and connection
			rset.close();
			stmt.close();
    	}catch (ServiceException e) {
			throw e;
		} catch (Exception e) {
			DBUtils.rollback(conn);
			throw new ServiceException("error occur inquiring countries");
		} finally {
			DBUtils.closeConnection(conn);
		}
    }
    
    Vector initUniv(String state){
    	Connection conn = null;
    	try {
    		conn = DBUtils.getConnection();
	    	Statement stmt = conn.createStatement();
			ResultSet rset=
			stmt.executeQuery("SELECT university FROM universities where location = '" + state + "' ORDER BY  university");
			while (rset.next()){
				u.add(rset.getString(1));
			}
			//close the result set, statement, and connection
			rset.close();
			stmt.close();
    	}catch (ServiceException e) {
			throw e;
		} catch (Exception e) {
			DBUtils.rollback(conn);
			throw new ServiceException("error occur inquiring countries");
		} finally {
			DBUtils.closeConnection(conn);
		}
    	return u;
    }
    void initDatabase(String path){
    	Connection conn = null;
    	String sql = "INSERT INTO universities (university, " +   // department number               
								                "location) " + 		// department ID
								                "values(?, ?)";
    	PreparedStatement pstmt = null;
        try{
        	conn = DBUtils.getConnection();
        	DBUtils.beginTransaction(conn);
        	pstmt = conn.prepareStatement(sql);
				
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(path)));
            String line = "";
            Vector tuple = new Vector();
            Vector univ = new Vector();
            String state = "";
            while((line = br.readLine()) != null) {
                //if we hit a blank line
                if(line.trim().length() == 0){
                    if(!univ.isEmpty()){
                        tuple.add(univ);
                        v.add(tuple);
                        
                        tuple = new Vector();
                        univ = new Vector();

                        //is there a next state?
                        if((line = br.readLine()) != null){                         
                            tuple.add(line.trim());
                        	state = line.trim();
                        }
                    }
                    //else this is the first entry, just add the state to the tuple
                    else if(tuple.isEmpty() && (line = br.readLine()) != null){                     
                         tuple.add(line.trim());
                         state = line.trim();
                    }
                }
                else{
                    univ.add(line.trim());
                    //insert university and state into table
                   // stmt.executeUpdate("INSERT INTO universities VALUES('" + line.trim() + "','" + state +  "');");
                    pstmt.setString(1, line.trim());
    				pstmt.setString(2, state);
    				pstmt.executeUpdate();
                }
            }  
            DBUtils.commit(conn);
        }catch (SQLException e) {
			System.out.println(e.toString());
			throw new DaoException("Error on adding dept", e);
		} catch (ServiceException e) {
			throw e;
		} catch (Exception e) {
			DBUtils.rollback(conn);
			throw new ServiceException("error occur inquiring countries");
		} finally {
			DBUtils.closeStatement(pstmt);
			DBUtils.closeConnection(conn);
		}
    }
}